import pymysql
import time
import datetime
from pandas import DataFrame
from loguru import logger
from common import *
import time
from loguru import logger

while True:
        try:
                with Db('data_collection_server') as conn:
                        print('connection successful')
                        cursor = conn.cursor()
                        while True:
                                try:
                                        #获取倒计时已到的数据
                                        a = time.time()
                                        sql = r'''
                                                select 
                                                source_id
                                                from data_source_count where countDown <= 0
                                        '''
                                        conn.ping(reconnect=True)
                                        cursor.execute(sql)
                                        data = cursor.fetchall()
                                        if len(data) != 0:
                                                ls_values = '('
                                                for i in data:
                                                        ls_values += str(i['source_id']) + ','
                                                ls_values = ls_values[:-1] + ')'
                                                try:
                                                        #wait表插入倒计时已到的数据
                                                        sql = r'''
                                                                insert  ignore into data_source_wait(
                                                                        source_id,
                                                                        spider_rule,
                                                                        spider_params,
                                                                        spider_type,
                                                                        data_type,
                                                                        url,
                                                                        status,
                                                                        collect_count,
                                                                        spiders,
                                                                        list_method,
                                                                        list_params,
                                                                        detail_method,
                                                                        detail_params,
                                                                        page_num,
                                                                        level,
                                                                        collect_time,
                                                                        source_file_path,
                                                                        template_id,
                                                                        manual,
                                                                        header,
                                                                        child_task,
                                                                        update_time,
                                                                        difference
                                                                        ) 
                                                                select 
                                                                        source_id,
                                                                        spider_rule,
                                                                        spider_params,
                                                                        spider_type,
                                                                        data_type,
                                                                        url,
                                                                        status,
                                                                        collect_count,
                                                                        spiders,
                                                                        list_method,
                                                                        list_params,
                                                                        detail_method,
                                                                        detail_params,
                                                                        page_num,
                                                                        level,
                                                                        collect_time,
                                                                        source_file_path,
                                                                        template_id,
                                                                        manual,
                                                                        header,
                                                                        child_task,
                                                                        update_time,
                                                                        difference
                                                                from data_source where source_id in {0}
                                                        '''.format(ls_values)
                                                        conn.ping(reconnect=True)
                                                        cursor.execute(sql)
                                                        conn.commit()
                                                except Exception as e:
                                                        print(e)
                                                        ls = [[str(datetime.datetime.now()), e]]
                                                        wrong = DataFrame(ls)
                                                        wrong.to_csv('wrong_data_source_refresh.csv', mode='a', header=None, index=None)
                                        # time.sleep至1秒空闲
                                        if (1 - time.time() + a) > 0:
                                                time.sleep(1 - time.time() + a)
                                        else:
                                                pass
                                        # 更新source表的倒计时
                                        conn.ping(reconnect=True)
                                        cursor.execute('update data_source_count set countDown = 3600+countDown where countDown <= 0')
                                        conn.commit()
                                        conn.ping(reconnect=True)
                                        cursor.execute('update data_source_count set countDown = countDown -1')
                                        conn.commit()
                                        logger.info('countDown')
                                except Exception as e:
                                        print(e)
                                        ls = [[str(datetime.datetime.now()),e]]
                                        wrong = DataFrame(ls)
                                        wrong.to_csv('wrong_data_source_refresh.csv',mode = 'a',header = None,index = None)
        except Exception as e:
                time.sleep(5*60)
                logger.debug('all wrong , the reason is {0}'.format(str(e)))

